const { pool } = require('../config/database');

class DatabaseService {
  // 代币相关操作
  static async saveToken(tokenData) {
    const { address, name, symbol, decimals, totalSupply, ownerAddress } = tokenData;
    const query = `
      INSERT INTO tokens (address, name, symbol, decimals, total_supply, owner_address)
      VALUES ($1, $2, $3, $4, $5, $6)
      ON CONFLICT (address) DO UPDATE SET
        name = EXCLUDED.name,
        symbol = EXCLUDED.symbol,
        decimals = EXCLUDED.decimals,
        total_supply = EXCLUDED.total_supply,
        owner_address = EXCLUDED.owner_address,
        updated_at = CURRENT_TIMESTAMP
      RETURNING *
    `;
    const values = [address, name, symbol, decimals, totalSupply, ownerAddress];
    const result = await pool.query(query, values);
    return result.rows[0];
  }

  static async getToken(address) {
    const query = 'SELECT * FROM tokens WHERE address = $1';
    const result = await pool.query(query, [address]);
    return result.rows[0];
  }

  static async getAllTokens() {
    const query = 'SELECT * FROM tokens ORDER BY created_at DESC';
    const result = await pool.query(query);
    return result.rows;
  }

  // 销售计划相关操作
  static async saveSalePlan(salePlanData) {
    const { 
      tokenAddress, 
      totalTokens, 
      price, 
      validityPeriod, 
      dividendInterval, 
      projectRev, 
      platformRev, 
      usdtToken, 
      startTime, 
      isActive 
    } = salePlanData;
    
    // Check if sale plan already exists
    const existing = await this.getSalePlan(tokenAddress);
    
    const values = [
      tokenAddress, totalTokens, price, validityPeriod, 
      dividendInterval, projectRev, platformRev, usdtToken, 
      startTime, isActive
    ];
    
    let result;
    if (existing) {
      // Update existing sale plan
      const updateQuery = `
        UPDATE sale_plans SET
          total_tokens = $2,
          price = $3,
          validity_period = $4,
          dividend_interval = $5,
          project_rev = $6,
          platform_rev = $7,
          usdt_token = $8,
          start_time = $9,
          is_active = $10,
          updated_at = CURRENT_TIMESTAMP
        WHERE token_address = $1
        RETURNING *
      `;
      result = await pool.query(updateQuery, values);
    } else {
      // Insert new sale plan
      const insertQuery = `
        INSERT INTO sale_plans (
          token_address, total_tokens, price, validity_period, 
          dividend_interval, project_rev, platform_rev, usdt_token, 
          start_time, is_active
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        RETURNING *
      `;
      result = await pool.query(insertQuery, values);
    }
    
    return result.rows[0];
  }

  static async getSalePlan(tokenAddress) {
    const query = 'SELECT * FROM sale_plans WHERE token_address = $1';
    const result = await pool.query(query, [tokenAddress]);
    return result.rows[0];
  }

  static async updateSalePlanStatus(tokenAddress, isActive) {
    const query = `
      UPDATE sale_plans 
      SET is_active = $2, updated_at = CURRENT_TIMESTAMP 
      WHERE token_address = $1
      RETURNING *
    `;
    const result = await pool.query(query, [tokenAddress, isActive]);
    return result.rows[0];
  }

  // 交易记录相关操作
  static async saveTransaction(transactionData) {
    const {
      tokenAddress,
      transactionHash,
      transactionType,
      fromAddress,
      toAddress,
      amount,
      usdtAmount,
      status,
      blockNumber,
      gasUsed
    } = transactionData;

    const query = `
      INSERT INTO transactions (
        token_address, transaction_hash, transaction_type, from_address,
        to_address, amount, usdt_amount, status, block_number, gas_used
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      ON CONFLICT (transaction_hash) DO UPDATE SET
        status = EXCLUDED.status,
        block_number = EXCLUDED.block_number,
        gas_used = EXCLUDED.gas_used
      RETURNING *
    `;

    const values = [
      tokenAddress, transactionHash, transactionType, fromAddress,
      toAddress, amount, usdtAmount, status, blockNumber, gasUsed
    ];

    const result = await pool.query(query, values);
    return result.rows[0];
  }

  static async getTransactions(tokenAddress, limit = 50) {
    const query = `
      SELECT * FROM transactions 
      WHERE token_address = $1 
      ORDER BY created_at DESC 
      LIMIT $2
    `;
    const result = await pool.query(query, [tokenAddress, limit]);
    return result.rows;
  }

  // 分红记录相关操作
  static async saveDividend(dividendData) {
    const { tokenAddress, roundNumber, amount, dividendToken } = dividendData;
    
    const query = `
      INSERT INTO dividends (token_address, round_number, amount, dividend_token)
      VALUES ($1, $2, $3, $4)
      RETURNING *
    `;
    
    const values = [tokenAddress, roundNumber, amount, dividendToken];
    const result = await pool.query(query, values);
    return result.rows[0];
  }

  static async getDividends(tokenAddress) {
    const query = `
      SELECT * FROM dividends 
      WHERE token_address = $1 
      ORDER BY round_number DESC
    `;
    const result = await pool.query(query, [tokenAddress]);
    return result.rows;
  }

  // 统计相关操作
  static async getTokenStats(tokenAddress) {
    const query = `
      SELECT 
        t.*,
        sp.total_tokens as sale_total_tokens,
        sp.price as sale_price,
        sp.is_active as sale_active,
        COUNT(DISTINCT tx.id) as transaction_count,
        COUNT(DISTINCT d.id) as dividend_count
      FROM tokens t
      LEFT JOIN sale_plans sp ON t.address = sp.token_address
      LEFT JOIN transactions tx ON t.address = tx.token_address
      LEFT JOIN dividends d ON t.address = d.token_address
      WHERE t.address = $1
      GROUP BY t.id, sp.total_tokens, sp.price, sp.is_active
    `;
    
    const result = await pool.query(query, [tokenAddress]);
    return result.rows[0];
  }

  // 清理旧数据
  static async cleanupOldData(daysOld = 30) {
    const query = `
      DELETE FROM transactions 
      WHERE created_at < NOW() - INTERVAL '${daysOld} days'
    `;
    const result = await pool.query(query);
    return result.rowCount;
  }
}

module.exports = DatabaseService;
